Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Traditional Disks

The layout for the OLTP system is quite straightforward. A minimal configuration looks something like this:


Element (# of Disks) Comments

System (1+) The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries.
Redo log (2+) At the very least, the OLTP system should have two log disks so that you can mirror the logs by means of Oracle log groups. If you have only two log disks, performance is degraded during archiving because the sequential nature of the log writes is disrupted.
Archive logs (1+) The number of disks needed for the archive log files is determined by the amount of data you have to archive. This data can be written to tape as necessary.
Data files (1+) The number of disks you need for data is determined by the amount of random I/O your user community generates.
Index files (1+) The number of disks needed for indexes is determined by the size of the indexes and the number of I/Os to the indexes.

Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, “Advanced Disk I/O Concepts,” remember that you can only push a disk drive to a maximum random I/O rate.

With traditional disk drives, you can stripe data among the various disks using either Oracle striping or OS striping. (I consider OS striping to be the same as using a disk array, as described in the following section.)

To stripe a table across multiple disks, you must first create the tablespace with multiple data files:

 CREATE TABLESPACE striped_tablespace
         DATAFILE 'file1' SIZE 1M,
                  'file2' SIZE 1M,
                  'file3' SIZE 1M,
                  'file4' SIZE 1M;

In this example, file1 is on disk 1, file2 is on disk 2, and so on. Once the tablespace is created, you cause the table to be striped when you create the table by making the number of extents equal to the number of data files:

 CREATE TABLE striped_table
              ( column1 number(4),
                column2 varchar2(40) )
   TABLESPACE striped_tablespace
      STORAGE ( INITIAL 512K NEXT 512K,
                MINEXTENTS 4 PCTINCREASE 0 );

In this example, the table is striped across all four of the data files. The problem with striping in this manner is that the size of the stripes is the size of the extent. Until all four extents have data in them, you access only one of the extents.

It can be difficult to manage the striping of hundreds of data files across hundreds of disks and to balance the load across these disks. If some tables are extremely hot (frequently accessed), you should stripe that table across many disk drives; cold tables (those infrequently accessed) can be striped across fewer disk drives.

It is the twin difficulty of configuration and load balancing that makes me prefer a disk array. When you use a disk array, these tasks are greatly simplified.

Disk Arrays

The layout for the OLTP system on a disk array is much simpler than it is on traditional disk drives. A minimal configuration looks something like this:


Element (# of Volumes) Comments

System (1+) The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries. If possible, this disk should be mirrored for additional fault tolerance.
Redo log (1+) There should be at least one log volume. This volume should be made up of at least two physical disks using RAID 1. If you use only one log volume, performance is degraded during archiving because the sequential nature of the log writes is disrupted.
Archive logs (1+) The number of disks needed for the archive log files is determined by the amount of data you need to archive. This data can be written to tape as necessary.
Data and index files(1+) Because you always get concurrent access to disks in a disk array, it is not necessary to split the data and indexes across separate volumes. The number of disks you need for data and index is determined by the amount of random I/O your user community generates.

Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, “Advanced Disk I/O Concepts,” remember that you can only push a disk drive to a maximum random I/O rate.

Unlike traditional disk drives, a disk array automatically stripes the data among all the disk drives. Therefore, you have to create only one tablespace and table for all your data. Although it is not necessary to put indexes into another tablespace, I recommend doing so for other reasons (such as monitoring and maintenance).

With traditional disk partitioning, it is difficult to manage hundreds of data files and hundreds of disks; with a disk array, on the other hand, it is possible to manage hundreds of disks with just a few data files. Of course, Oracle has a 2 gigabyte limitation on the size of a data file, but this is easily resolved by creating a data file for every 2 gigabytes of space you need. The data files can all reside on the same disk array volume.

The fact that some tables are hot and others are cold is irrelevant because all data access is uniformly distributed among all the disks. Load balancing is not an issue because the small size of the stripe ensures that random data accesses are most likely spread out among all the disks in the array.

By using a disk array, you greatly simplify many of the management and load balancing tasks. With the disk array, you also have the option of using fault tolerance without affecting system performance. Of course, using fault tolerance requires significantly more disks.

I recommend that you use a disk array if possible. Software striping is fine, but if your system is under heavy loads—as is typical in an OLTP system—you achieve better performance by offloading the striping overhead to a hardware RAID controller.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.